package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.dao.base.RegionDao;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-4-9
 * Time: 下午9:28
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class ZjjhDao extends SimpleHibernateTemplate {
    public Page<Map> findList(String type, String jhnf, String jhyf,Long gysId, Long sbgsId, Long sbbmId, String zt, String jhbh,int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        List<Object> param = new ArrayList<Object>();
        List<String> condition = new ArrayList<String>();
        //id,计划单号,物资计划类型,物资计划编号,供应商,客户,物资申报公司,支付方式,自购类型,备注,资金计划申报公司,申报人,申报日期,状态
        String sqlBase = " select z.id as id,z.jhbh as jhbh,xy.xyhm as xyhm,z.lc_id as processInstanceId,'Zgzjjh' as type,w.jhbh as wzjhbh,gs.qc as gysmc," +
                " kh.qc as khmc,wzjhsbgs.qc as wzjhsbgsmc,zffs.z as zffs,z.bz as bz,zjjhsbgs.jc as zjjhsbgsmc,zjjhsbbm.qc as zjjhsbbmmc," +
                " sbr.id as sbrid,sbr.xm as sbr,z.sbrq as sbrq,z.zt as zt,z.jhnf as jhnf,z.jhyf as jhyf,z.sbgs_id as sbgsId,z.sbbm_id as sbbmId,z.gys_id as gysId,z.date_created,z.sbje as sbje,sbr.zggs_id,sbr.zgjg_id, " +
                " z.CREATEORGAN_ID,z.CREATEDEPARTMENT_ID,z.CREATEUSER_ID "+
                " from T_BIZ_ZGZJJH z " +
                " left join T_BIZ_CGWZJH w on z.zgwzjh_id=w.id " +
                " left join T_BIZ_XY xy on w.xy_id=xy.id " +
                " left join T_SYS_GSJG gs on z.gys_id=gs.id " +
                " left join T_SYS_GSJG kh on z.kh_id=kh.id " +
                " left join T_SYS_GSJG wzjhsbgs on w.sbgs_id=wzjhsbgs.id " +
                " left join T_SYS_GSJG zjjhsbgs on z.sbgs_id=zjjhsbgs.id " +
                " left join T_SYS_GSJG zjjhsbbm on z.sbbm_id=zjjhsbbm.id " +
                " left join T_SYS_SJZDZ zffs on z.sjzdz_zffs_id=zffs.id " +
                " left join T_SYS_YG sbr on z.createuser_id=sbr.id " +
                " where z.deleted=0" +
                " union all " +
                " select t.id as id,t.jhbh as jhbh,xy.xyhm as xyhm,t.lc_id as processInstanceId,'Tpzjjh' as type,w.jhbh as wzjhbh,gs.qc as gysmc,'' as khmc," +
                " wzjhsbgs.qc as wzjhsbgsmc,zffs.z as zffs,t.bz as bz,zjjhsbgs.qc as zjjhsbgsmc,zjjhsbbm.qc as zjjhsbbmmc," +
                " sbr.id as sbrid,sbr.xm as sbr,t.sbrq as sbrq,t.zt as zt,t.jhnf as jhnf,t.jhyf as jhyf,t.sbgs_id as sbgsId,t.sbbm_id as sbbmId,t.gys_id as gysId,t.date_created,t.sbje as sbje,sbr.zggs_id,sbr.zgjg_id, " +
                " t.CREATEORGAN_ID,t.CREATEDEPARTMENT_ID,t.CREATEUSER_ID "+
                " from T_BIZ_TPZJJH t " +
                " left join T_BIZ_CGWZJH w on t.tpwzjh_id=w.id " +
                " left join T_BIZ_XY xy on w.xy_id=xy.id " +
                " left join T_SYS_GSJG gs on t.gys_id=gs.id " +
                " left join T_SYS_GSJG wzjhsbgs on w.sbgs_id=wzjhsbgs.id " +
                " left join T_SYS_GSJG zjjhsbgs on t.sbgs_id=zjjhsbgs.id " +
                " left join T_SYS_GSJG zjjhsbbm on t.sbbm_id=zjjhsbbm.id " +
                " left join T_SYS_SJZDZ zffs on t.sjzdz_zffs_id=zffs.id " +
                " left join T_SYS_YG sbr on t.createuser_id=sbr.id " +
                " where t.deleted=0";
        if (StringUtils.isNotEmpty(type)) {
            condition.add(" type=? ");
            param.add(type);
        }
        if (StringUtils.isNotEmpty(jhnf)) {
            condition.add(" jhnf=? ");
            param.add(jhnf);
        }
        if (StringUtils.isNotEmpty(jhyf)) {
            condition.add(" jhyf=? ");
            param.add(jhyf);
        }
        if (gysId != null) {
            condition.add(" gysId=? ");
            param.add(gysId);
        }
        if (sbgsId != null) {
            condition.add(" sbgsId=? ");
            param.add(sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" sbbmId=? ");
            param.add(sbbmId);
        }
        if(StringUtils.isNotEmpty(jhbh)){
            condition.add(" jhbh like \'%"+jhbh+"%\' ");
        }
        if (StringUtils.isNotEmpty(zt)) {
            condition.add(" zt=? ");
            param.add(zt);
        }
        String whereSql = "";
        String dataRegion = RegionDao.createSQLDataRegion(null, "v");
        if (!condition.isEmpty())
            whereSql = " where " + dataRegion + (condition.isEmpty() ? "" : " and ") + StringUtils.join(condition, " and ");
        String orderSql = " order by v.date_created desc";
        String countSql = "select count(1) from (" + sqlBase + ") v " + whereSql;
        String resultSql = " select id as \"id\",jhbh as \"jhbh\",xyhm as \"xyhm\",processInstanceId as \"processInstanceId\",type as type,wzjhbh as \"wzjhbh\",gysmc as \"gysmc\",khmc as \"khmc\", " +
                " wzjhsbgsmc as \"wzjhsbgsmc\",zffs as \"zffs\",bz as \"bz\",zjjhsbgsmc as \"zjjhsbgsmc\",zjjhsbbmmc as \"zjjhsbbmmc\", " +
                " sbrid as sbrid,sbr as \"sbr\",sbrq as \"sbrq\",zt as zt,jhnf as jhnf,jhyf as jhyf,sbgsId as sbgsId,sbbmId as sbbmId,gysId as gysId,date_created,sbje as \"sbje\",zggs_id,zgjg_id, " +
                " CREATEORGAN_ID,CREATEDEPARTMENT_ID,CREATEUSER_ID "+
                " from (" + sqlBase + ") v " + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql, param.toArray()).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql, param.toArray())
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询电子借款单的付款金额
     *
     * @param jhId
     * @param jhlx zgzjjh tpzjjh
     * @return
     */
    public Double findFkje(Long jhId, String jhlx) {
        String sql = "select nvl(sum(mx.bcfkmx),0) from t_biz_sfkmx mx" +
                "  left join t_biz_sfk sfk on (mx.fksh_id = sfk.id)" +
                "  left join t_biz_jkd jkd on (jkd.id = mx.jkd_id)" +
                "  left join t_biz_@jhlx zjjh on (jkd.@jhlx_id = zjjh.id)" +
                "where sfk.jiuqi_insert_flag = 3 and sfk.deleted = 0 and jkd.deleted = 0 and zjjh.id = ?";
        sql = sql.replaceAll("@jhlx", jhlx);
        SQLQuery sqlQuery = createSQLQuery(sql, jhId);
        return ((BigDecimal) sqlQuery.uniqueResult()).doubleValue();
    }

    /**
     * 首页显示资金计划总数
     *
     * @param jhnf
     * @param jhyf
     * @param zt
     * @return
     */
    public String findZjjhSum(String jhnf, String jhyf, String zt) {
        String dataRegion = RegionDao.createSQLDataRegion(null, "v");
        String zjjhSql = "select count(1) from (select tt.zt,tt.jhnf,tt.jhyf,sbr.zgjg_id,sbr.zggs_id, " +
                " tt.CREATEORGAN_ID,tt.CREATEDEPARTMENT_ID,tt.CREATEUSER_ID "+
                "from t_biz_tpzjjh tt left join t_sys_yg sbr on tt.createuser_id=sbr.id " +
                " where  tt.deleted = 0 " +
                "union all " +
                "select tz.zt,tz.jhnf,tz.jhyf,sbr.zgjg_id,sbr.zggs_id, " +
                " tz.CREATEORGAN_ID,tz.CREATEDEPARTMENT_ID,tz.CREATEUSER_ID "+
                "from t_biz_zgzjjh tz left join t_sys_yg sbr on tz.createuser_id=sbr.id " +
                "where tz.deleted = 0 ) v where jhnf = ? and jhyf = ? and zt = ? " +  (StringUtils.isEmpty(dataRegion) ? "" : "and " + dataRegion);;
        BigDecimal zjjhSum = (BigDecimal) createSQLQuery(zjjhSql, jhnf, jhyf, zt).uniqueResult();
        return zjjhSum.toString();
    }
}
